<?php

namespace App\Http\Controllers\manage;

use App\app_comment;
use App\Http\Controllers\Controller;
use Illuminate\Database\QueryException;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Storage;
use Validator;

class excelController extends Controller
{
    public function index(Request $request)
    {
        //当前页面标题
        $title = '从Excel导入评论数据';

        if ($request->method() == 'POST') {
            $validator = Validator::make($request->input(), array());
            if ($request->file('file') == null) {
                $validator->errors()->add('file', '请选择上传的文件！');
                return back()->withErrors($validator);
            } else {
                $type = $request->file('file')->getMimeType();
                if ($type == "application/vnd.ms-excel") {
                    $type = 'xls';
                } elseif ($type == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") {
                    $type = 'xlsx';
                } else {
                    $validator->errors()->add('file', '仅支持Excel 97-2003 工作簿(.xls) / Excel 工作簿(.xlsx) 上传的文件格式不正确！');
                    return back()->withErrors($validator);
                }
                $path = $request->file('file')->storeAs('xls', md5(time() . rand(0, 10000)) . '.' . $type);
                $path = str_replace('/', '\\', $path);
                $result = $this->input($type, storage_path('app\\' . $path));
                Storage::delete($path);
                if ($result['OK'] == -1 || $result['Failed'] == -1) {
                    $validator->errors()->add('file', '文件或系统错误，请联系管理员');
                    return back()->withErrors($validator);
                }
                $validator->errors()->add('file', '处理' . ($result['OK'] + $result['Failed']) . '条记录 成功：' . $result['OK'] . '条 ' . '失败：' . $result['Failed'] . '条');
                for ($i = 0; $i < count($result['failedLine']); $i++) {
                    $validator->errors()->add('line' . $i, '行' . $result['failedLine'][$i] . '插入失败');
                }
                return back()->withErrors($validator);
            }
        } else {
            return view("manage/excel", compact('title'));
        }
    }

    /**
     * @param $fileType
     * @param $filePath
     * @return array 返回成功数和失败数目
     */
    public function input($fileType, $filePath)
    {
        if (strcasecmp($fileType, 'xls') == 0) {
            //Excel2003格式文件
            $PHPReader = new \PHPExcel_Reader_Excel5();
        } elseif (strcasecmp($fileType, 'xlsx') == 0) {
            //Excel2007格式文件
            $PHPReader = new \PHPExcel_Reader_Excel2007();
        } else {
            //文件格式错误
            return array('OK' => -1, 'Failed' => -1);;
        }

        //读取Excel文件
        $objPHPExcel = $PHPReader->load($filePath, 'utf-8');

        $objSheets = [];
        //使用ExcelConfig配置信息读取内容
        foreach (ExcelConfig()['sheets'] as $sheet) {
            array_push($objSheets, $objPHPExcel->getSheet($sheet));
        }

        //数据库对象数组(一个对象表示一条数据库记录)
        $comments = [];

        foreach ($objSheets as $objSheet) {
            $highestRow = $objSheet->getHighestRow();       //当前工作簿总行数
            //$highestColumn = $objSheet->getHighestColumn(); //当前工作簿总列数

            for ($row = 1; $row <= $highestRow; $row++) {
                if (ExcelConfig()['hasHeader']) {
                    if (in_array($row, ExcelConfig()['header'])) continue;
                }

                //从配置中读取列信息
                $columns = array_keys(ExcelConfig()['columns']);
                $comment = new app_comment();
                for ($columnIndex = 0; $columnIndex < count($columns); $columnIndex++) {
                    $DBColumn = ExcelConfig()['columns'][$columns[$columnIndex]];
                    $comment->$DBColumn =
                        $objSheet->getCell($columns[$columnIndex] . $row)->getCalculatedValue();
                }
                array_push($comments, $comment);
            }
        }
        $countOK = 0;
        $countFailed = 0;
        $failedLine = [];
        for ($i = 0; $i < count($comments); $i++) {
            try {
                if ($comments[$i]->save() == 1) {
                    $countOK++;
                } else {
                    $countFailed++;
                    array_push($failedLine, $i + 1 + count(ExcelConfig()['header']));
                }
            } catch (QueryException $e) {
                $countFailed++;
                array_push($failedLine, $i + 1 + count(ExcelConfig()['header']));
            }
        }
        return array('OK' => $countOK, 'Failed' => $countFailed, 'failedLine' => $failedLine);
    }
}
